<?php

include 'includes/db.php';

function is_admin($username){

	$sql="SELECT name FROM users WHERE name='$username' and is_admin=1";
	$result = mysql_query($sql);
	
	if (mysql_num_rows($result) == 0) {
		return false;
	}
	else {
		return true;
	}

}

function user_exists($username) {
	$sql="SELECT name FROM users WHERE name='$username'";
	$result = mysql_query($sql);

	if (mysql_num_rows($result) == 0) {
		return false;
	}
	else {
		return true;
	}
}

function create_user($username, $password, $is_admin) {
	$sql="INSERT INTO users (name, is_admin) VALUES ('$username', $is_admin)";
	if (user_exists($username)) {
		echo "ERROR: User already exists";
	}
	else {
		mysql_query($sql);
	}
}
		
function get_userid($username){
	$sql="SELECT user_id FROM users WHERE name='$username'";
	$result = mysql_query($sql);

	if (mysql_num_rows($result) == 0) {
		return -1;
	}
	else {
		$user_id = mysql_fetch_array($result)['user_id'];
		return $user_id;
	}
}

function list_active_users(){
	$sql="SELECT user_id, name, is_admin FROM users_active ORDER BY name";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>User ID</th><th>User Name</th><th>Is Admin?</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$user_id = $row['user_id'];
		$user_name = $row['name'];
		$is_admin = $row['is_admin'];

		echo "<tr><td>$user_id</td><td><a href=users.php?action=view&user_id=$user_id>$user_name</a></td><td>$is_admin</td><td><a href=users.php?action=deactivate&user=$user_name>Deactivate</a></tr>";
	}
	echo "</table>";
}

function list_inactive_users(){
	$sql="SELECT user_id, name, is_admin FROM users_inactive ORDER BY name";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>User ID</th><th>User Name</th><th>Is Admin?</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$user_id = $row['user_id'];
		$user_name = $row['name'];
		$is_admin = $row['is_admin'];

		echo "<tr><td>$user_id</td><td>$user_name</td><td>$is_admin</td><td><a href=users.php?action=activate&user=$user_name>Activate</a></tr>";
	}
	echo "</table>";
}

function deactivate_user($username){
	$user_id = get_userid($username);
	if (is_admin($_SERVER['PHP_AUTH_USER'])) {
		$sql="UPDATE users SET is_active=0 where user_id=$user_id";
		mysql_query($sql);
	}
	else {
		echo "Error: Insufficient privilege to modify users";
	}
}

function activate_user($username){

	$user_id = get_userid($username);
	if (is_admin($_SERVER['PHP_AUTH_USER'])) {
		$sql="UPDATE users SET is_active=1 where user_id=$user_id";
		mysql_query($sql);
	}
	else {
		echo "Error: Insufficient privilege to modify users";
	}
}

function view_user($user_id) {

	$sql="SELECT user_id, name, is_admin, is_active FROM users WHERE user_id='$user_id'";

	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);

	$username = $row['name'];
	($row['is_active']==1 ? $is_active="Yes": $is_active="No");
	($row['is_admin']==1 ? $is_admin="Yes": $is_admin="No");



	echo "<h3> User Information </h3><br/>";
	echo "<b>User ID: </b>".$user_id."<br />";
	echo "<b>Username: </b>".$username."<br />";
	echo "<b>Active: </b>".$is_active."<br />";
	echo "<b>Admin: </b>".$is_admin."<br />";

	echo "<h3> Documents Locked By User `$username` </h3>";

	$sql = "select * from doc_checkout join documents where doc_id = document_id and user_id=$user_id";
	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Document ID</th><th>Document Name</th><th>Document Description</th><th>Date Locked</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$doc_id = $row['doc_id'];
		$doc_name = $row['name'];
		$doc_desc = $row['description'];
		$checkout_date = $row['date'];

		echo "<tr><td>$doc_id</td><td>$doc_name</td><td>$doc_desc</td><td>$checkout_date</td><td><a href=documents.php?action=discard_checkout&doc_id=$doc_id>Discard</a></td></tr>";
	}
	echo "</table>";

}

function has_read($user_id,$doc_id){
	$sql="SELECT * FROM doc_permissions WHERE user_id=$user_id and doc_id=$doc_id and has_read=1";
	$result = mysql_query($sql);
	if (mysql_num_rows($result) !=0) {
		return true;
	}
	else {
		return false;
	}
}

function has_write($user_id,$doc_id){
	$sql="SELECT * FROM doc_permissions WHERE user_id=$user_id and doc_id=$doc_id and has_write=1";
	$result = mysql_query($sql);
	if (mysql_num_rows($result) !=0) {
		return true;
	}
	else {
		return false;
	}
}

function is_checked_out($doc_id){
	$sql="select * from documents join doc_checkout where document_id = doc_id and doc_id=$doc_id";
	$result = mysql_query($sql);

	if (mysql_num_rows($result) == 0) {
		return false;
	}
	else {
		return true;
	}
}	

function checkout_document($doc_id){
	$cur_user=$_SERVER['PHP_AUTH_USER'];
	$user_id=get_userid($cur_user);
	$sql="INSERT INTO doc_checkout (user_id,doc_id,date) VALUES ($user_id,$doc_id,NOW())";
	if (!is_checked_out($doc_id)){
		$result=mysql_query($sql);
	}
}

function list_checkouts_for_user($username) {
	$sql="SELECT documents.document_id, documents.name, documents.description FROM users natural join doc_checkout join documents WHERE doc_id = document_id and users.name='$username'";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Document Name</th><th>Document Description</th><th></th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$doc_name = $row['name'];
		$doc_desc = $row['description'];
		$doc_id = $row['document_id'];

		echo "<tr><td><a href=documents.php?action=view&doc_id=$doc_id> $doc_name</a></td><td>$doc_desc</td><td><a href=documents.php?action=check_in_form&doc_id=$doc_id>Check-in</a></td><td><a href=documents.php?action=discard_checkout&doc_id=$doc_id>Discard</a></td></tr>";
	}
	echo "</table>";
}

function discard_checkout($username,$doc_id)
{
	$user_id = get_userid($username);
	$cur_user = $_SERVER['PHP_AUTH_USER'];
	if (($username == $cur_user) || (is_admin($cur_user)) ) {
		$sql="DELETE FROM doc_checkout where user_id='$user_id' and doc_id='$doc_id'";
		mysql_query($sql);
	}
	else {
		echo "Error: Insufficient privilege to discard checkout";
	}
}

function check_in_document($doc_id,$ver_desc,$pkg_id)
{

	$cur_user = $_SERVER['PHP_AUTH_USER'];
	$user_id = get_userid($cur_user);

	// does user have checkout?

	$sql="select count(*) as count from doc_checkout where doc_id=$doc_id and user_id=$user_id";
	$result = mysql_query($sql);

	$count = mysql_fetch_array($result)['count'];

	if ($count > 0) {  //user has checkout, safe to create new version
		//get current version
		$sql="SELECT MAX(ver_no) as max FROM versions WHERE doc_id = $doc_id";
		$result = mysql_query($sql);
		$cur_ver = mysql_fetch_array($result)['max'];
		$cur_ver++;

		//insert new version
		$sql="INSERT INTO versions (doc_id,ver_no,date,description,package_id) VALUES ($doc_id,$cur_ver,NOW(),'$ver_desc',$pkg_id)";
		$result = mysql_query($sql);

		//discard lock

		discard_checkout($cur_user,$doc_id);

	}
	else {
		echo "Error:  User does not hold checkout on document.";
	}

}


function discard_all_checkouts($username)
{
	$user_id = get_userid($username);
	$cur_user = $_SERVER['PHP_AUTH_USER'];
	if (($username == $cur_user) || (is_admin($cur_user)) ) {
		$sql="DELETE FROM doc_checkout where user_id='$user_id'";
		mysql_query($sql);
	}
	
	else {
		echo "Error: Insufficient privilege to discard checkout";
	}
}


function list_all_documents() {

$sql="SELECT documents.document_id, documents.name, documents.description FROM documents";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Document Name</th><th>Document Description</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$doc_name = $row['name'];
		$doc_desc = $row['description'];
		$doc_id = $row['document_id'];

		echo "<tr><td><a href=documents.php?action=view&doc_id=$doc_id> $doc_name</a></td><td>$doc_desc</td></tr>";
	}
	echo "</table>";
}

function manage_perms() {

$sql="SELECT documents.document_id, documents.name, documents.description FROM documents";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Document ID</th><th>Document Name</th><th>Document Description</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$doc_name = $row['name'];
		$doc_desc = $row['description'];
		$doc_id = $row['document_id'];

		echo "<tr><td>$doc_id</td><td><a href=documents.php?action=view&doc_id=$doc_id> $doc_name</a></td><td>$doc_desc</td><td><a href=documents.php?action=viewperms&doc_id=$doc_id> Permissions </a></tr>";
	}
	echo "</table>";
}


function view_doc($doc_id) {

	$sql="SELECT document_id, name, description FROM documents WHERE document_id='$doc_id'";
        
	$cur_user = $_SERVER['PHP_AUTH_USER'];
	$cur_userid = get_userid($cur_user);

	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);

	echo "<h3> Document Information </h3><br/>";
	echo "<b>Document ID: </b>".$row['document_id']."<br />";
	echo "<b>Document Name: </b>".$row['name']."<br />";
	echo "<b>Document Description: </b>".$row['description']."<br />";

	if (has_write($cur_userid,$doc_id) || is_admin($cur_user)) {

		if (is_checked_out($doc_id)){
			echo "<b>Checked out.</b><br />";
		}
		else {
			echo "<b><a href=documents.php?action=checkout&doc_id=$doc_id>Check Out Document</a></b><br />";
		}
	}

	if (has_read($cur_userid,$doc_id) || is_admin($cur_user)) {

		echo "<b><a href=pub/file.dat>Download Document</a></b><br />";
	}


	echo "<h3> Version History </h3>";

	$sql = "select ver_no, versions.description, sw_packages.name, versions.date from versions join sw_packages where versions.package_id = sw_packages.package_id and doc_id = $doc_id";
	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Version</th><th>Description</th><th>Software Package</th><th>Date</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$ver_no = $row['ver_no'];
		$ver_desc = $row['description'];
		$ver_sw = $row['name'];
		$ver_date = $row['date'];

		echo "<tr><td>$ver_no</td><td>$ver_desc</td><td>$ver_sw</td><td>$ver_date</td></tr>";
	}
	echo "</table>";

}

function view_perms($doc_id) {

	$sql="SELECT document_id, name, description FROM documents WHERE document_id='$doc_id'";
        
	$cur_user = $_SERVER['PHP_AUTH_USER'];
	$cur_userid = get_userid($cur_user);

	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);

	echo "<h3> Document Information </h3><br/>";
	echo "<b>Document ID: </b>".$row['document_id']."<br />";
	echo "<b>Document Name: </b>".$row['name']."<br />";
	echo "<b>Document Description: </b>".$row['description']."<br />";

	echo "<h3> Permissions </h3>";

	$sql="select u.user_id, u.name, p.doc_id, p.has_read, p.has_write from users u left join (select * from doc_permissions where doc_id=$doc_id) as p ON u.user_id = p.user_id order by u.user_id";

	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>User ID</th><th>User Name</th><th>Read?</th><th>Write?</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$user_id = $row['user_id'];
		$doc_name = $row['name'];
		$has_read = ($row['has_read']?'checked':'');
		$has_write = ($row['has_write']?'checked':'');
		echo "<tr><td>$user_id</td><td>$doc_name</td><td><form action=documents.php?action=setperms&doc_id=$doc_id&user_id=$user_id method=\"post\"><input type=\"checkbox\" name=\"has_read\" value=\"1\" $has_read/></td><td> <input type=\"checkbox\" name=\"has_write\" value=\"1\" $has_write/></td><td><input type=\"submit\" value=\"Update\"></form></td></tr>";
	}
	echo "</table>";

}

function set_perms($user_id,$doc_id,$has_read,$has_write){
	$sql="insert into doc_permissions (user_id,doc_id,has_read,has_write) values ($user_id,$doc_id,$has_read,$has_write) on duplicate key update has_write=$has_write, has_read=$has_read;";

	$cur_user = $_SERVER['PHP_AUTH_USER'];

	if ( is_admin($cur_user) ) {

		mysql_query($sql);
	}
	else {
		echo "Insufficient privileges to set document permissions.";
	}

}



function create_document($doc_name,$doc_desc){

	$cur_user = $_SERVER['PHP_AUTH_USER'];
	$cur_userid = get_userid($cur_user);

//create document	
	$sql="INSERT INTO documents (name, description) VALUES ('$doc_name','$doc_desc')";
	$result = mysql_query($sql);

//find doc_id of new document
	$sql="SELECT MAX(document_id) as doc_id,name,description from documents where name='$doc_name' and description='$doc_desc'";
	$result = mysql_query($sql);

	$row = mysql_fetch_array($result);
	$doc_id = $row['doc_id'];

//set read and write on document	

	$sql="INSERT INTO doc_permissions (user_id,doc_id,has_read,has_write) VALUES ($cur_userid,$doc_id,1,1)";
	mysql_query($sql);

}


function list_all_software(){
	$sql = "SELECT package_id, name, description FROM sw_packages";
	
	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Package ID</th><th>Package Name</th><th>Description</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$pkg_id = $row['package_id'];
		$pkg_name = $row['name'];
		$pkg_desc = $row['description'];

		echo "<tr><td>$pkg_id</td><td><a href=software.php?action=view&pkg_id=$pkg_id>$pkg_name</a></td><td>$pkg_desc</td></tr>";
	}
	echo "</table>";
}

function edit_packages(){
	$sql = "SELECT package_id, name, description FROM sw_packages";
	
	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Package ID</th><th>Package Name</th><th>Description</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$pkg_id = $row['package_id'];
		$pkg_name = $row['name'];
		$pkg_desc = $row['description'];

		echo "<tr><td>$pkg_id</td><td><a href=software.php?action=view&pkg_id=$pkg_id>$pkg_name</a></td><td>$pkg_desc</td><td><a href=software.php?action=update_package&pkg_id=$pkg_id>Edit</a></td></tr>";
	}
	echo "</table>";
}


function view_pkg($pkg_id) {

	$sql="SELECT package_id, name, description FROM sw_packages WHERE package_id='$pkg_id'";

	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);

	echo "<h3> Package Information </h3><br/>";
	echo "<b>Package ID: </b>".$row['package_id']."<br />";
	echo "<b>Package Name: </b>".$row['name']."<br />";
	echo "<b>Description: </b>".$row['description']."<br />";

	echo "<h3> Documents Associated With This Software Package </h3>";

	$sql = " select doc_id,ver_no,documents.name,documents.description as d_desc,versions.description as v_desc from versions join documents where doc_id = document_id and package_id = $pkg_id";
	$result = mysql_query($sql);

	echo "<table>";
	echo "<tr><th>Document ID</th><th>Document Name</th><th>Document Description</th><th>Version No.</th><th>Version Description</th></tr>";

	while ($row = mysql_fetch_array($result)) {
		$doc_id = $row['doc_id'];
		$ver_no = $row['ver_no'];
		$doc_name = $row['name'];
		$doc_desc = $row['d_desc'];
		$ver_desc = $row['v_desc'];

		echo "<tr><td>$doc_id</td><td>$doc_name</td><td>$doc_desc</td><td>$ver_no</td><td>$ver_desc</td></tr>";
	}
	echo "</table>";

}

function create_package($pkg_name,$pkg_desc){
	$sql="INSERT INTO sw_packages (name, description) VALUES ('$pkg_name','$pkg_desc')";
	mysql_query($sql);
}

function mod_pkg($pkg_id,$pkg_name,$pkg_desc){
	$sql="UPDATE sw_packages SET name='$pkg_name', description='$pkg_desc' where package_id = $pkg_id";
	mysql_query($sql);
}


function update_pkg_form($pkg_id) {

	$sql="SELECT package_id,name,description FROM sw_packages where package_id=$pkg_id";

	$result = mysql_query($sql);

	$row = mysql_fetch_array($result);

	$pkg_name = $row['name'];
	$pkg_id = $row['package_id'];
	$pkg_desc = $row['description'];


	echo '<h3> Update Software Package </h3><br />';
	echo '<form action ="software.php?action=mod_pkg&pkg_id='.$pkg_id.'" method="post">';
        echo 'Package Name <input type="text" value="'.$pkg_name.'" name="pkg_name"><br />';
        echo 'Description: <input type="text" value="'.$pkg_desc.'" name="pkg_desc"><br />';
        echo '<input type="Submit" value="Submit">';
        echo '</form>';

}


function package_dropdown(){   //returns <select> containing all sw packages
	$sql="SELECT package_id,name FROM sw_packages";

	$result = mysql_query($sql);

	echo "<SELECT name=\"package\">";

	while ($row = mysql_fetch_array($result)){
		$pkg_id = $row['package_id'];
		$pkg_name = $row['name'];
		echo '<option value="'.$pkg_id.'"> '.$pkg_name.'</option>';
	}
	echo "</select>";
}

